﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using GS.DataConnection;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;

namespace GS.CMS.Entities
{
    public class Reports
    {
        public static DataTable GetTable( SqlConnection strConnection, string StoreName, string TableName, Hashtable htparams)
        {
            SqlCommand Com = GetCommand(strConnection,StoreName, htparams);
            try
            {
                SqlDataAdapter Da = new SqlDataAdapter(Com);
                DataSet Ds = new DataSet();
                Da.Fill(Ds, TableName);
                return Ds.Tables[TableName];
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                Com.Connection.Close();
                Com.Dispose();
            }
        }
        private static SqlCommand GetCommand(SqlConnection strConnection, string StoreName, Hashtable ParName)
        {
            SqlCommand Com = new SqlCommand(StoreName, strConnection);
            Com.CommandType = CommandType.StoredProcedure;
            IDictionaryEnumerator myList = ParName.GetEnumerator();
            while (myList.MoveNext())
            {
                if (myList.Value is string)
                {
                    if (myList.Key.ToString().IndexOf("@n_") == 0)
                    {
                        Com.Parameters.Add(myList.Key.ToString(), SqlDbType.NVarChar);
                        Com.Parameters[myList.Key.ToString()].Value = myList.Value;
                        continue;
                    }
                    if (myList.Key.ToString().IndexOf("@nt_") == 0)
                    {
                        Com.Parameters.Add(myList.Key.ToString(), SqlDbType.NText);
                        Com.Parameters[myList.Key.ToString()].Value = myList.Value;
                        continue;
                    }
                    if (myList.Key.ToString().IndexOf("@t_") == 0)
                    {
                        Com.Parameters.Add(myList.Key.ToString(), SqlDbType.Text);
                        Com.Parameters[myList.Key.ToString()].Value = myList.Value;
                        continue;
                    }
                    Com.Parameters.Add(myList.Key.ToString(), SqlDbType.VarChar);
                    Com.Parameters[myList.Key.ToString()].Value = myList.Value;
                    continue;
                }

                if (myList.Value is int)
                {
                    Com.Parameters.Add(myList.Key.ToString(), SqlDbType.Int);
                    Com.Parameters[myList.Key.ToString()].Value = myList.Value;
                    continue;
                }

                if (myList.Value is bool)
                {
                    Com.Parameters.Add(myList.Key.ToString(), SqlDbType.Bit);
                    Com.Parameters[myList.Key.ToString()].Value = myList.Value;
                    continue;
                }
                if (myList.Value is char)
                {
                    Com.Parameters.Add(myList.Key.ToString(), SqlDbType.VarChar, 1);
                    Com.Parameters[myList.Key.ToString()].Value = myList.Value;
                    continue;
                }
                if (myList.Value is byte)
                {
                    Com.Parameters.Add(myList.Key.ToString(), SqlDbType.TinyInt);
                    Com.Parameters[myList.Key.ToString()].Value = myList.Value;
                    continue;
                }

                if (myList.Value is DateTime)
                {
                    Com.Parameters.Add(myList.Key.ToString(), SqlDbType.DateTime);
                    Com.Parameters[myList.Key.ToString()].Value = myList.Value;
                    continue;
                }
                if (myList.Value is Guid)
                {
                    Com.Parameters.Add(myList.Key.ToString(), SqlDbType.UniqueIdentifier);
                    Com.Parameters[myList.Key.ToString()].Value = myList.Value;
                    continue;
                }
                if (myList.Value is decimal)
                {
                    Com.Parameters.Add(myList.Key.ToString(), SqlDbType.Money);
                    Com.Parameters[myList.Key.ToString()].Value = myList.Value;
                    continue;
                }
            }
            return Com;
        }
        private string GetConnectionString(string str)
        {
            //variable to hold our return value
            string conn = string.Empty;
            //check if a value was provided
            if (!string.IsNullOrEmpty(str))
            {
                //name provided so search for that connection
                conn = ConfigurationManager.ConnectionStrings[str].ConnectionString;
            }
            else
            //name not provided, get the 'default' connection
            {
                conn = ConfigurationManager.ConnectionStrings["YourConnName"].ConnectionString;
            }
            //return the value
            return conn;
        }

        public DataTable ThuTienNgoaiTruTheoBienLai(DateTime TuNgay,DateTime DenNgay)
        {
            System.Collections.Hashtable ht = new System.Collections.Hashtable();
            ht.Add("@TuNgay", TuNgay);
            ht.Add("@DenNgay", DenNgay);
            ht.Add("@NhanVien_id", null);
            ht.Add("@Khoa_ID",null);
            string strConn = GetConnectionString("ITO_db");
            SqlConnection Conn = new SqlConnection(strConn);
            return GetTable(Conn, "sp_BCVP_ITO01_BangKeSoBienLaiChiTiet", "dtReport", ht);
        }
        #region "Constructor"
        public Reports()
        {
            /* Huynh Van Sang
             * Date :02/04/2009
             */
        }
        #endregion
    }
}
